const mysql = require('mysql');
import mySqlConnConfig from "./mySqlConnConfig";
import uuid from "node-uuid";

/*---Data Store 连接池---*/
const pool = mysql.createPool(mySqlConnConfig);

pool.on("connection", function() { console.log("创建一个连接") });
//当一个回掉压入队伍等待连接的时候触发入队事件
pool.on("enqueue", function() { console.log("入队") });

/*---sql 集合---*/
const sqls = {
    dbConnTest: () => {
        return `SELECT * FROM booksell.test;`;
    },
    userRegister: () => {
        return ``;
    },
    userLogin: () => {
        return ``;
    },
    bookSellRelease:(bookinfo)=>{
        let info = {};
        for(let key in bookinfo){
            if(bookinfo[key] || bookinfo[key] == 0) info[key] = bookinfo[key]
            else info[key] = ""
        }
        return `INSERT INTO booksell.bookinfo 
                (bid, uid, name, author, publisher, ISBN, grade, type, 
                cover, legend_1, legend_2, legend_3, description, 
                price, origin_price, getSelf, inventory, relstatus, position, address) 
            VALUES 
                ('${info.bid}', '${info.uid}', '${info.name}', '${info.author}', '${info.publisher}'
                , '${info.ISBN}', '${info.grade}', '${info.type}'
                , '${info.cover}', '${info.legend_1}', '${info.legend_2}', '${info.legend_3}', '${info.description}'
                , '${info.price}', '${info.origin_price}', '${info.getSelf}', '${info.inventory}', '${info.relstatus}'
                , '${info.position}', '${info.address}');`;
    },
    getBookinfo:(bid,uid) => {
        return  `select * from 
        (SELECT booksell.bookinfo.*, nickname,head_url,stuAuth,signature FROM booksell.bookinfo, booksell.userinfo
          where booksell.bookinfo.uid = booksell.userinfo.uid
            and bookinfo.bid="${bid}") as bb,
        (SELECT count(fid) as isFavo FROM booksell.favorites 
          where bid="${uid}" and uid="${bid}") as ff`;
    },
    getMeOnSellBookList:(uid)=>{
        return `SELECT * FROM booksell.bookinfo 
            where uid = "${uid}"
            order by releasetime desc`;
    },
    getMefavorites:(uid)=>{
        return `select bb.* from 
        (SELECT booksell.bookinfo.*, nickname,head_url,stuAuth FROM booksell.bookinfo, booksell.userinfo
          where booksell.bookinfo.uid = booksell.userinfo.uid) as bb
        where bb.bid in 
         (SELECT uid as bid FROM booksell.favorites 
           where bid="${uid}")`;
    },
    setFavorite:(bid,uid,isFavo)=>{
        if(isFavo) {
            let fid = uuid.v1();
            return `insert into  booksell.favorites
                (fid,uid,bid,buildtime) values ("${fid}","${uid}","${bid}",now());`
        }else{
            return `delete from  booksell.favorites
                where bid="${bid}" and uid="${uid}" and fid != "";`
        }
    },
    sendComment:(commentObj)=>{
        let {cid,bid,uid,comment,head_url,nickname,} = commentObj;
        return `insert into booksell.comments
            (cid,bid,uid,comment,head_url,nickname,buildtime) 
        values 
            ("${cid}","${bid}","${uid}","${comment}","${head_url}","${nickname}",now());`;
    },
    pullChatRecords:(msgObj)=>{
        let {mid,isdel,
            fromUid,fnickname,fhead_url,fstuAuth,
            toUid,tnickname,thead_url,tstuAuth,
            type,message,sendtime,readtime,
            bid,cover,name,despcription,price,origin_price
        } = msgObj;
        return `insert into booksell.chatrecords
                (
                    mid,isdel,buildtime,fromUid,fnickname,fhead_url,fstuAuth
                    ,toUid,tnickname,thead_url,tstuAuth
                    ,type,message,sendtime,readtime
                    ,bid,cover,name,despcription,price,origin_price
                ) 
                values 
                (
                    "${mid}","${isdel}",now()
                    ,"${fromUid}","${fnickname}","${fhead_url}","${fstuAuth}"
                    ,"${toUid}","${tnickname}","${thead_url}","${tstuAuth}"
                    ,"${type}","${message}","${sendtime}","${readtime}"
                    ,"${bid}","${cover}","${name}","${despcription}","${price}","${origin_price}"
                );`
    },
    searchBooksByKey:(keyWord)=>{
        return `SELECT * FROM booksell.bookinfo
            where name like "%${keyWord}%" or author like "%${keyWord}%" or publisher like "%${keyWord}%";`
    }

}



/*---销毁(释放)连接池---*/
const poolDestory = () => {}

// sql查询 公共响应
const sqlResponse = (err, res, callback) => {
    if (err) callback({ code: "err", errMsg: err });
    else callback({ code: "01", data: res });
}

/*
 * @ 数据库连接测试
 **/
export const dbConnTest = (callback) => {
    pool.getConnection(function(error, connection) {
        let sql = `SELECT * FROM booksell.test;`;
        connection.query(
            sql,
            (err, rows) => {
                if (err) callback({ code: "err", errMsg: err });
                else callback({ code: "01", data: rows });
            }
        );
        connection.release(); // 释放连接
    });
}

//用户注册
export const userRegister = (uid, nickname, password, phonenum, callback) => {
    pool.getConnection(function(error, connection) {
        // let sql = sqlCollection.userRegister(nikename, password, phonenum);
        connection.query(
            `insert into userinfo (uid,nickname,password,phonenum,buildtime) values ('${uid}','${nickname}','${password}','${phonenum}',now())`,
            (err, res) => {
                if (err) {
                    callback({
                        code: "04",
                    });
                } else {
                    callback({ code: "01", data: res.insertId, msg: "successful" });
                }
            }
        );
        connection.release();
    });
}


//用户登录
export const userLogin = (phonenum, password, callback) => {
    // 获取连接
    pool.getConnection(function(error, connection) {
        // let sql = sqlCollection.userLogin(phonenum, password);
        connection.query(
            `select * from userinfo where password="${password}" and (phonenum="${phonenum}" or nickname="${phonenum}");`,
            (err, rows) => {
                if (err) callback({ code: "err", errMsg: err });
                else callback({ code: "01", msg: "ok", data: rows });
            }
        );
        connection.release(); // 释放连接
    });
}

//用户登录验证码登录
export const userLogins = (phonenum, callback) => {
    // 获取连接
    pool.getConnection(function(error, connection) {
        // let sql = sqlCollection.userLogin(phonenum, password);
        connection.query(
            `select * from userinfo where (phonenum="${phonenum}" or nickname="${phonenum}");`,
            (err, rows) => {
                if (err) callback({ code: "err", errMsg: err });
                else callback({ code: "01", msg: "ok", data: rows });
            }
        );
        connection.release(); // 释放连接
    });
}

//用户信息修改
export const userUpdate = (nickname, phonenum, uid, email, age, userHeadUrl, signature, callback) => {
    pool.getConnection(function(error, connection) {
        // let sql = sqlCollection.userLogin(phonenum, password);
        connection.query(
            `update userinfo set nickname='${nickname}',phonenum='${phonenum}',email='${email}',age='${age}',signature='${signature}',head_url='${userHeadUrl}' where uid='${uid}'`,
            (err, rows) => {
                if (err) {
                    callback({ code: "err", errMsg: err });
                } else {
                    callback({ code: "01", msg: "ok", data: null });
                }
            }
        );
        connection.release(); // 释放连接
    });
}

//用户学生信息认证
export const userApprove = (realname, collage, uid, sex, callback) => {
        pool.getConnection(function(error, connection) {
            // let sql = sqlCollection.userLogin(phonenum, password);
            connection.query(
                `update userinfo set stuAuth='1',realname='${realname}',collage='${collage}',sex='${sex}' where uid='${uid}'`,
                (err, rows) => {
                    if (err) {
                        console.log(err);
                        callback({ code: "err", errMsg: err });
                    } else {
                        callback({code:"01",msg:"ok"})
                    }
                }
            )
            connection.release(); // 释放连接
        });
    }

/*
 * @ 图书信息发布
 * @ author: Tjn
 * @ params: bookinfo, callback
 **/
export const bookSellRelease = (bookinfo, callback) => {
    pool.getConnection(function(error, connection) {
        console.log(1,error);
        let sql = sqls.bookSellRelease(bookinfo);
        connection.query(
            sql,
            (err, rows) => {
                if (err) {
                    console.log(2,err);
                    callback({ code: "err", errMsg: err });
                } else {
                    callback({ code: "01", msg: "ok", data: rows });
                }
            }
        );
        connection.release(); // 释放连接
    });
}

/*
 * @ 获得我的在售列表
 * @ author: Tjn
 * @ params: uid, callback
 **/
export const getMeOnSellBookList = (uid, callback)=>{
    pool.getConnection(function(error, connection) {
        let sql = sqls.getMeOnSellBookList(uid);
        connection.query(
            sql,
            (err, rows) => {
                if (err) {
                    callback({ code: "err", errMsg: err });
                } else {
                    callback({ code: "01", msg: "ok", data: rows });
                }
            }
        );
        connection.release(); // 释放连接
    });
}

/*
 * @ 获得我的收藏列表
 * @ author: Tjn
 * @ params: uid, callback
 **/
export const getMefavorites = (uid, callback)=>{
    pool.getConnection(function(error, connection) {
        let sql = sqls.getMefavorites(uid);
        connection.query(
            sql,
            (err, rows) => {
                if (err) {
                    callback({ code: "err", errMsg: err });
                } else {
                    callback({ code: "01", msg: "ok", data: rows });
                }
            }
        );
        connection.release(); // 释放连接
    });
}

/*
 * @ 获图书收藏
 * @ author: Tjn
 * @ params: bid, uid, isFavo(收藏 | 取消),callback
 **/
export const setFavorite = (bid,uid,isFavo,callback)=>{
    pool.getConnection(function(error, connection) {
        let sql = sqls.setFavorite(bid,uid,isFavo);
        connection.query(
            sql,
            (err, rows) => {
                if (err) {
                    callback({ code: "err", errMsg: err });
                } else {
                    callback({ code: "01", msg: "ok", data: rows });
                }
            }
        );
        connection.release(); // 释放连接
    });
}


//FavoriteBookList 获取大众喜欢书籍列表
export const FavoriteBookList=callback=>{
    pool.getConnection(function(error, connection) {
        // let sql = sqlCollection.userLogin(phonenum, password);
        connection.query(
            `select * from bookinfo where  bookinfo.relstatus=1`,
            (err, rows) => {
                console.log(err);
                if (err){
                    callback({ code: "err", errMsg: err });
                }else{
                    if(rows.length>=0){
                        for(var i=0;i<rows.length;i++){
                            delete rows[i].password
                        }
                    }
                    callback({code:"01",msg:"ok",data:rows.slice(0,4)});
                } 
            }
        );
        connection.release(); // 释放连接
    });
}


// 获取附近在售列表
export const getBookOnSellNearBy = (filter, callback) => {
    console.log("filter", filter)
    pool.getConnection((error, connection) => {
        let sql = `SELECT * FROM booksell.bookinfo;`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err) callback({ code: "err", errMsg: err });
                else callback({ code: "01", data: rows });
            }
        )
        connection.release(); // 释放连接
    });
}



/*-----  后端pc ------*/
// 后台读取 数据库

// 管理员登录
export const management = (username,callback) => { 
    pool.getConnection(function(error,connection) {
        connection.query(
            `select * from admininfo`
        ,(err,resp) => {
            if(err){
               callback({code:"05",msg:"err",data:null})
            }else{
                callback({code:"01",msg:"ok",data:resp})
            }
        })
        connection.release(); // 释放连接
    })
}


// 用户管理

export const pc_userinfo = (callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`select * from userinfo`,(err,resp) => {
            if(err){
                callback({code:"05",msg:"err",data:null})
            }else{
                callback({code:"01",msg:"ok",data:resp})
            }
        })
        connection.release(); // 释放连接
    })
}

export const pc_bookinfo = (callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`select * from bookinfo`,(err,resp) => {
            if(err){
                callback({code:"05",msg:"err",data:null})
            }else{
                callback({code:"01",msg:"ok",data:resp})
            }
        })
        connection.release(); // 释放连接
    })
}
// 图书状态修改
export const pc_pass = (bid,value,callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`update bookinfo set relstatus='${value}' where bid='${bid}'`,(err,resp) =>{
            callback({code:"01",msg:"ok",data:resp})
        })
        connection.release(); // 释放连接
    })
}

// 活动列表
export const pc_hot = (callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`select * from shopinfo`,(err,resp) => {
            if(err){
                callback({code:"04",msg:"err",data:null})
            }else{
                callback({code:"01",msg:"ok",data:resp})
            }
        })
        connection.release(); // 释放连接
    })
}

//添加新的活动数据
export const pc_addHot = (Sid,shopName,shopNum,shopType,collage,startTime,endTime,stuAuth,price,callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`insert into shopinfo (Sid,shopName,shopNum,shopType,collage,startTime,endTime,stuAuth,price) values ('${Sid}','${shopName}','${shopNum}','${shopType}','${collage}','${startTime}','${endTime}','${stuAuth}','${price}')`,(err,resp) =>{
            callback({code:"01",msg:"ok",data:resp})
        })
        connection.release(); // 释放连接
    })
}

// 修改活动状态

export const pc_changeHot = (Sid,value,callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`update shopinfo set stuAuth='${value}' where Sid='${Sid}'`,(err,response) =>{
            connection.query(`select * from  shopinfo `,(err,response) =>{
                console.log(Sid,123);
                callback({code:"01",msg:"ok",data:response})
            })
        })
        connection.release(); // 释放连接
    })
}


// 删除活动数据
export const pc_delHot = (Sid,callback) =>{
    pool.getConnection(function(error,connection) {
        connection.query(`delete from shopinfo where  Sid='${Sid}'`,(err,response) =>{
            callback({code:"01",msg:"ok",data:response})
        })
        connection.release(); // 释放连接
    })
}




/*-----  后端pc ------*/



/*
获取书籍详情信息
*/
export const getBookinfo= (bid,uid, callback) => {
    pool.getConnection((error, connection) => {
        let sql = sqls.getBookinfo(bid,uid);
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    console.log(err);
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}


//修改书籍的价格
export const getAlterthepriceBook= (bid,price,callback) => {
    pool.getConnection((error, connection) => {
        let sql = `update bookinfo set price='${price}' where bid='${bid}'`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}


//添加用户的订单地址

export const getAddaddress= (name,ipone,uid,city,cityStr,detailed,isdefault,aid,callback) => {
    pool.getConnection((error, connection) => {
        let sql = `insert into addressinfo (name,ipone,uid,city,cityStr,detailed,isdefault,aid,buildtime) values 
        ('${name}','${ipone}','${uid}','${city}','${cityStr}','${detailed}','${isdefault}','${aid}',now())`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01",msg:"ok", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}

//编辑用户的订单地址
export const updateAddaddress= (aid,name,ipone,city,cityStr,detailed,isdefault,callback) => {
    pool.getConnection((error, connection) => {
        let sql = `update addressinfo set name='${name}',ipone='${ipone}',city='${city}',cityStr='${cityStr}',detailed='${detailed}',isdefault='${isdefault}'
        where aid='${aid}'`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01",msg:"ok", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}

//删除用户地址
export const delAddress= (aid,callback) => {
    pool.getConnection((error, connection) => {
        let sql = `delete from addressinfo where aid='${aid}'`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01",msg:"ok", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}

//获取全部订单地址
export const getAlladdress= (uid,callback) => {
    pool.getConnection((error, connection) => {
        let sql = `select * from addressinfo where uid='${uid}'`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01",msg:"ok", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}

export const sendComment = (commentObj,callback)=>{
    pool.getConnection((error, connection) => {
        let sql = sqls.sendComment(commentObj);
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows });
            }
        )
        connection.release(); // 释放连接
    });
}

/* 获取评论信息
*
*/ 
export const getBookComments = (bid,callback)=>{
    pool.getConnection((error, connection) => {
        let sql = `SELECT * FROM booksell.comments
            where bid="${bid}"
            order by buildtime desc;`;
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows });
            }
        )
        connection.release(); // 释放连接
    });
}

//添加商品订单
export const getorderLists = (uid,rece_address,paytype,paystatus, paysum,list,callback)=>{
    pool.getConnection((error, connection) => {
        let oid=uuid.v1()
        let sql1 = `insert into orders (oid,uid,rece_address,paytype,paystatus,paysum,postage,buildtime)
        values ('${oid}','${uid}','${rece_address}','${paytype}','${paystatus}','${paysum}','10',now());
        `;
        let sql2=""
        for(let i=0;i<list.length;i++){
            var {bid,name,cover,num,price,description} = list[i]
            sql2+=`insert into goods (gid,bid,oid,name,cover,count,price,description,buildtime)
            values ('${uuid.v1()}','${bid}','${oid}','${name}','${cover}','${num}','${price}','${description}',now());
            `
        }
        let sql =`begin; 
            ${sql1} ${sql2} 
        commit;`
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err){
                    console.log(err);
                    callback({ code: "err", errMsg: err });
                }else{
                    callback({ code: "01",msg:"ok", data: rows });
                } 
            }
        )
        connection.release(); // 释放连接
    });
}

//查询未支付的订单
export const getOrder = (oid,callback)=>{
    let sql=`select * from goods,orders where orders.oid='${oid}' and orders.oid=goods.oid`
    pool.getConnection((error,connection)=>{
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows });
            }
        )
        connection.release(); // 释放连接
    })
        
}

//更改支付订单的状态
export const updateOrder = (oid,callback)=>{
    
}

//获取热搜页面
export const getHotLists =callback=>{
    let sql=`select * from bookinfo where relstatus='1'`
    pool.getConnection((error,connection)=>{
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows.slice(0,5) });
            }
        )
        connection.release(); // 释放连接
    })
        
}
//获取全部订单
export const getAllorders =callback=>{
    let sql=`select * from orders`
    pool.getConnection((error,connection)=>{
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows});
            }
        )
        connection.release(); // 释放连接
    })
        
}

//获取订单对应商品

export const getGoods =(oid,callback)=>{
    let sql=`select * from goods where oid='${oid}'`
    pool.getConnection((error,connection)=>{
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows});
            }
        )
        connection.release(); // 释放连接
    })
        
}

//获取支付或未支付
export const getorders =(paystatus,callback)=>{
    let sql=`select * from orders where paystatus='${paystatus}'`
    pool.getConnection((error,connection)=>{
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows});
            }
        )
        connection.release(); // 释放连接
    })
        
}



/* 获取用户聊天记录
* 
*/ 
export const getMeMessageAll = (uid, callback) => {
    let sql = `SELECT * FROM booksell.chatrecords
    where isdel <> 1 and (fromUid = "${uid}" or toUid = "${uid}")
        and fromUid != toUid
    order by sendtime asc;`;
    pool.getConnection((error, connection) => {
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows });
            }
        )
        connection.release(); // 释放连接
    });
}

/* 写入聊天记录
*
*/
export const pullChatRecords = (msgObj, callback) => {
    let sql = sqls.pullChatRecords(msgObj);
    pool.getConnection((error, connection) => {
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: null });
            }
        )
        connection.release(); // 释放连接
    });
}

/*---图书分类查询---*/
export const searchBookByType = (typeFilter, callback)=>{
    let sql = `SELECT * FROM booksell.bookinfo
        where type like "%${typeFilter}%";`;
    pool.getConnection((error, connection) => {
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows });
            }
        )
        connection.release(); // 释放连接
    });
}

/*--- 图书关键词搜索 ---*/
export const searchBooksByKey = (typeFilter, callback)=>{
    let sql = sqls.searchBooksByKey(typeFilter);
    pool.getConnection((error, connection) => {
        if(error) return callback({ code: "05", errMsg: error });
        connection.query(
            sql,
            (err, rows) => {
                if (err)  callback({ code: "err", errMsg: err });
                else callback({ code: "01",msg:"ok", data: rows });
            }
        )
        connection.release(); // 释放连接
    });
}

